package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialDutyDao;
import com.ph.irp.model.PBaseAssistantmaterialDuty;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialDutyDaoImpl implements
		PBaseAssistantmaterialDutyDao {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addPBaseAssistantmaterialDuty(PBaseAssistantmaterialDuty duty)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append("insert into p_base_assistantmaterial_duty (DutyCode,DutyName) values( ");
		sb.append("'").append(duty.getDutyCode().trim()).append("',");
		sb.append("'").append(duty.getDutyName().trim()).append("')");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void deletePBaseAssistantmaterialDuty(int dutyId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sql = "delete from p_base_assistantmaterial_duty where DutyID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, dutyId);
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public PBaseAssistantmaterialDuty getDutyById(int dutyId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseAssistantmaterialDuty duty = null;
		String sql = "select * from p_base_assistantmaterial_duty where DutyID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, dutyId);
			rs = pStmt.executeQuery();
			if (rs.next()) {
				duty = new PBaseAssistantmaterialDuty();
				duty.setDutyId(rs.getInt("DutyID"));
				duty.setDutyCode(rs.getString("DutyCode"));
				duty.setDutyName(rs.getString("DutyName"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return duty;
	}

	@Override
	public List<PBaseAssistantmaterialDuty> getDutyList() throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDuty> list = new ArrayList<PBaseAssistantmaterialDuty>();

		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_duty");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialDuty pbad = new PBaseAssistantmaterialDuty();
				pbad.setDutyId(rs.getInt("DutyID"));
				pbad.setDutyCode(rs.getString("DutyCode"));
				pbad.setDutyName(rs.getString("DutyName"));
				list.add(pbad);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("finally")
	@Override
	public boolean isSame(String dutyCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Boolean b = null;
		String sql = "select * from p_base_assistantmaterial_duty where DutyCode='"
				+ dutyCode.trim() + "'";

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if (rs.next()) {
				b = true;
			} else {
				b = false;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return b;
	}

	@Override
	public void updatePBaseAssistantmaterialDuty(PBaseAssistantmaterialDuty duty)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_assistantmaterial_duty set DutyCode='");
		sb.append(duty.getDutyCode().trim()).append("',DutyName='");
		sb.append(duty.getDutyName().trim()).append("'");
		sb.append(" where DutyID=").append(duty.getDutyId());

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public List<PBaseAssistantmaterialDuty> getDutyByCode(String code)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDuty> list = new ArrayList<PBaseAssistantmaterialDuty>();

		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_base_assistantmaterial_duty where DutyCode like '%"
						+ code + "%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialDuty pbad = new PBaseAssistantmaterialDuty();
				pbad.setDutyId(rs.getInt("DutyID"));
				pbad.setDutyCode(rs.getString("DutyCode"));
				pbad.setDutyName(rs.getString("DutyName"));
				list.add(pbad);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@Override
	public List<PBaseAssistantmaterialDuty> getDutyByCodeAndName(String code,
			String name) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDuty> list = new ArrayList<PBaseAssistantmaterialDuty>();

		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_base_assistantmaterial_duty where DutyCode like '%"
						+ code + "%' and DutyName like '%" + name + "%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialDuty pbad = new PBaseAssistantmaterialDuty();
				pbad.setDutyId(rs.getInt("DutyID"));
				pbad.setDutyCode(rs.getString("DutyCode"));
				pbad.setDutyName(rs.getString("DutyName"));
				list.add(pbad);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@Override
	public List<PBaseAssistantmaterialDuty> getDutyByName(String name)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDuty> list = new ArrayList<PBaseAssistantmaterialDuty>();

		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_base_assistantmaterial_duty where DutyName like '%"
						+ name + "%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialDuty pbad = new PBaseAssistantmaterialDuty();
				pbad.setDutyId(rs.getInt("DutyID"));
				pbad.setDutyCode(rs.getString("DutyCode"));
				pbad.setDutyName(rs.getString("DutyName"));
				list.add(pbad);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

}
